import datetime

from Common import sql_content

class getRule(object):
    def __init__(self):
        self.sql_content = sql_content.sql_content()
    def getAllrule(self):
        rule_all = []
        con = self.sql_content.conten()
        sql_into = "select * from setrule "
        cursor = con.cursor()
        try:
            cursor.execute(sql_into)
            rs = cursor.fetchall()
            for row in rs:
                list_row = list(row) #变成列表
                rule_all.append(list_row)
        except Exception as e:
            print(e)
            con.rollback()
        cursor.close()
        con.close()
        print(rule_all)
        return rule_all
    def getAlltopic(self):
        topic = []
        con = self.sql_content.conten()
        sql_topic = "select COLUMN_NAME from information_schema.COLUMNS where table_name ='setrule'"
        cursor = con.cursor()
        try:
            cursor.execute(sql_topic)
            rs = cursor.fetchall()
            for row in rs:
                list_row = list(row)  # 变成列表
                topic.append(list_row)
        except Exception as e:
            print(e)
            con.rollback()
        cursor.close()
        con.close()
        return topic
    def setOnerule(self,data):
        con = self.sql_content.conten()
        cursor = con.cursor()
        # "update data_collection.setrule set web_Name = "zhangsan", limit_Type = 2 where ruleId = 1;"
        sql_insert = "update data_collection.setrule set " \
                     "web_Name = '%s', entrance_URL= '%s' ,execute_Script = '%s', storage_Table= '%s' , time_Week= '%s' , time_Day_start= '%s' , time_Day_end= '%s' , " \
                     "set_Time= '%s' , limit_Type= '%s' , limit_Value= '%s' , remark= '%s' WHERE ruleId = %d" \
                     % (data['web_Name'], data['entrance_URL'], data['execute_Script'], data['storage_Table'], data['time_Week'],
                        data['time_Day_start'], data['time_Day_end'],data['set_Time'], data['limit_Type'],
                        data['limit_Value'], data['remark'],data['ruleId'])
        try:
            print(sql_insert)
            cursor.execute(sql_insert)
            con.commit()
            print(cursor.rowcount)
        except Exception as e:
            print(e)
            # 回滚
            con.rollback()
        cursor.close()
        con.close()
if __name__ == "__main__":
    key = ['ruleId',
        'web_Name',
        'entrance_URL',
        'execute_Script',
        'storage_Table',
        'time_Week',
        'time_Day_start',
        'time_Day_end',
        'set_Time',
        'limit_Type',
        'limit_Value',
        'remark'

    ]
    data = [1, 'fangzhen', 'http://search.51job.com/list/000000,000000,0100,01,9,99,%2B,2,1.html?lang=c&stype=1&postchannel=0000&workyear=99&cotype=99&degreefrom=99&jobterm=99&companysize=99&lonlat=0%2C0&radius=-1&ord_field=0&confirmdate=9&fromType=&dibiaoid=0&address=&line=&specialarea=00&from=&welfare=', 'Job51_shezhao/spider_main.py', 'data_51job_she', '3-7', '1:00', '24:59', datetime.datetime(2016, 11, 15, 10, 49), '2', '21', '上次爬取时间：2019/5/20']
    thisDict =dict(map(lambda x, y: [x, y], key, data))
    print(thisDict)
